﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace mamnontuoitho7.AppCode
{
    public class Article
    {
        public ConnectGetData _connectData;
        public Article()
        {
            _connectData = new ConnectGetData();
         }


        public bool ArticleCreateBySP(String ProductTypeID, String MemberID, String Title, String Introduce, String ProductStatusID, String ViewNumber, String PostUser, String ArticleTypeID, String Description, String Content, String Photo)
        {
            SqlConnection con = _connectData.GetConnect();
            
            try
            {
                SqlCommand command = new SqlCommand("sp_ArticleInsert", con);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ProductTypeID", SqlDbType.NVarChar).Value = ProductTypeID;
                command.Parameters.Add("@MemberID", SqlDbType.NVarChar).Value = MemberID;
                command.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Title;
                command.Parameters.Add("@Introduce", SqlDbType.NVarChar).Value = Introduce;
                command.Parameters.Add("@ProductStatusID", SqlDbType.NVarChar).Value = ProductStatusID;
                command.Parameters.Add("@ViewNumber", SqlDbType.NVarChar).Value = ViewNumber;
                command.Parameters.Add("@PostUser", SqlDbType.NVarChar).Value = PostUser;
                command.Parameters.Add("@ArticleTypeID", SqlDbType.NVarChar).Value = ArticleTypeID;
                command.Parameters.Add("@Description", SqlDbType.NVarChar).Value = Description;
                command.Parameters.Add("@Content", SqlDbType.NVarChar).Value = Content;
                command.Parameters.Add("@Photo", SqlDbType.NVarChar).Value = Photo;

                con.Open();
                command.ExecuteNonQuery();
                con.Dispose();
                con.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("SQL Error" + ex.Message.ToString());
                return false;
            }
            return true;
        }

        /// <summary>
        /// update article information except photo
        /// </summary>
        /// <param name="productID"></param>
        /// <param name="articleTypeID"></param>
        /// <param name="title"></param>
        /// <param name="introduce"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public bool ArticleUpdate(String productID, String articleTypeID, String title, String introduce, String content)
        {
            SqlConnection con = _connectData.GetConnect();
            con.Open();
             try
            {
                string sql = "update Product set Title = @Title, Introduce = @Introduce, [EditDate] = default where ProductID = @ProductID";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddWithValue("@ProductID", productID);
            cmd.Parameters.AddWithValue("@Title", title);
            cmd.Parameters.AddWithValue("@Introduce", introduce);
            cmd.ExecuteNonQuery();
            cmd.Dispose();

            string sql2 = "update Article set ArticleTypeID = @ArticleTypeID, Content = @Content where ArticleID = @ArticleID";
            SqlCommand cmd2 = new SqlCommand(sql2, con);
            cmd2.Parameters.AddWithValue("@ArticleTypeID", articleTypeID);
            cmd2.Parameters.AddWithValue("@Content", content);
           
            cmd2.Parameters.AddWithValue("@ArticleID", productID);
            cmd2.ExecuteNonQuery();
            cmd2.Dispose();
            con.Close();

            }
             catch (Exception ex)
             {
                 System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                 return false;
             }
             return true;
        }

       

        /// <summary>
        /// update photo in article table
        /// </summary>
        /// <param name="productID"></param>
        /// <param name="photo"></param>
        public void ArticlePhotoUpdate(String productID, String photo)
        {
            SqlConnection con = _connectData.GetConnect();
            con.Open();
             try
            {
            string sql = "update Article set Photo = @Photo where ArticleID = @ProductID";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddWithValue("@ProductID", productID);
            cmd.Parameters.AddWithValue("@Photo", photo);
           
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
            }
             catch (Exception ex)
             {
                 System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                 
             }
        }


        /// <summary>
        /// get article list from ArticleListView in admin page
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllArticle(String amount)
        {
            string sql = "";
            if (amount == "0")
            {
                sql = "select * from ArticleListView ORDER BY ProductID DESC";
            }
            else
            {
                sql = "select top(" + amount + ") * from ArticleListView ORDER BY ProductID DESC";
            }
            DataTable dt = _connectData.GetDataTable(sql);

            return dt;
        }

        /// <summary>
        /// get article list from ArticleListView showing to user page
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllArticleToUser()
        {
            string sql = "select * from ArticleListView where ProductStatusName='Enable' ORDER BY ProductID DESC";

            DataTable dt = _connectData.GetDataTable(sql);

            return dt;
        }



        /// <summary>
        /// get some articles list from ArticleListView ex : "tin mới nhất" trang chủ
        /// </summary>
        /// <returns></returns>
        public DataTable GetSomeArticle(String amount)
        {
            string str = "select top(" + amount + ") * from ArticleListView where ProductStatusName='Enable' and ArticleTypeID != 1 and ArticleTypeID != 11 ORDER BY ProductID DESC";
            return _connectData.GetDataTable(str);
           
        }



        /// <summary>
        /// get article based on type
        /// </summary>
        /// <param name="ArticleTypeID"></param>
        /// <returns></returns>
        public DataTable GetArticleByType(String articleTypeID, String amount)
        {
            if (articleTypeID == "0")
            {
                if (amount == "0")
                {
                    return _connectData.GetDataTable("select * from ArticleListView ORDER BY ProductID DESC");
                }
                else
                {
                    return _connectData.GetDataTable("select top(" + amount + ") * from ArticleListView ORDER BY ProductID DESC");
                }
            }
            else
            {
                if (amount == "0")
                {
                    return _connectData.GetDataTable("select * from ArticleListView where ArticleTypeID = '" + articleTypeID + "' ORDER BY ProductID DESC");
                }
                else
                {
                    return _connectData.GetDataTable("select top(" + amount + ") * from ArticleListView where ArticleTypeID = '" + articleTypeID + "' ORDER BY ProductID DESC");
                }
            }
        }

        /// <summary>
        /// get information about one article
        /// </summary>
        /// <param name="productID"></param>
        /// <returns></returns>
        public DataTable GetArticleByID(String productID)
        {

            SqlConnection con = _connectData.GetConnect();
            string sql = "select * from ArticleProductView where ProductID = @ProductID and ProductStatusID=0 ";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddWithValue("@ProductID", productID);
            DataTable dt = _connectData.GetDataTable(cmd);
            return dt;
        }


        /// <summary>
        /// get information about one article
        /// </summary>
        /// <param name="productID"></param>
        /// <returns></returns>
        public DataTable GetArticleByIDAdmin(String productID)
        {

            SqlConnection con = _connectData.GetConnect();
            //           con.Open();

            string sql = "select * from ArticleProductView where ProductID = @ProductID";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddWithValue("@ProductID", productID);
            DataTable dt = _connectData.GetDataTable(cmd);
            //         con.Close();
            return dt;
        }

        /// <summary>
        /// get a number of record of a page
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="step"></param>
        /// <returns>datatable</returns>
        public DataTable GetArticlePaged(int pageIndex, int step, String articleTypeID)
        {
            //int startRowIndex = pageIndex * step + 1;
            //int endRowIndex = startRowIndex + step -1;
            int startRowIndex = (pageIndex - 1) * step + 1;
            int endRowIndex = startRowIndex + step - 1;
            string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY ProductID DESC) AS number, * FROM ArticleListView where ArticleTypeID = " + articleTypeID + " and ProductStatusName = 'enable' ) AS TempTable WHERE number BETWEEN @startRowIndex AND @endRowIndex";
            SqlConnection con = _connectData.GetConnect();
      //      con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);

            // cmd.Parameters.AddWithValue("@TableName", tableName);
            cmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
            cmd.Parameters.AddWithValue("@endRowIndex", endRowIndex);
            DataTable dt = _connectData.GetDataTable(cmd);
     //       con.Close();
            return dt;
        }

        /// <summary>
        /// get total number of article in ONE type
        /// </summary>
        /// <param name="articleTypeID"></param>
        /// <returns>int</returns>
        public global::System.Nullable<int> TotalArticleOfOneType(String articleTypeID)
        {

            SqlConnection con = _connectData.GetConnect();

            con.Open();

            string sql = "SELECT COUNT(*)FROM ArticleListView WHERE ArticleTypeID =" + articleTypeID + " and ProductStatusName = 'enable'";
            SqlCommand cmd = new SqlCommand(sql, con);

            object returnValue;

            returnValue = cmd.ExecuteScalar();
            con.Close();
            if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
            {
                return new global::System.Nullable<int>();
            }
            else
            {
                return new global::System.Nullable<int>(((int)(returnValue)));
            }
        }

        /// <summary>
        /// get some top article in ont type : dành  cho các link bài viết liên quan
        /// </summary>
        /// <param name="amount"></param>
        /// <param name="articleTypeID"></param>
        /// <returns>datatable</returns>
        public DataTable GetSomeTopArticleByType(String amount, String articleTypeID)
        {
            SqlConnection con = _connectData.GetConnect();
       
            try
            {
                string sql = "select top(@Amount) ProductID,Title,PostDate from ArticleListView where ProductStatusName='Enable' and ArticleTypeID=@ArticleTypeID ORDER BY ProductID DESC";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@Amount", Convert.ToInt16(amount));
                cmd.Parameters.AddWithValue("@ArticleTypeID", articleTypeID);
                DataTable dt = _connectData.GetDataTable(cmd);
     
                return dt;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return null;
            }
        }


        /// <summary>
        /// Get Sub Article based on ArticleTypeID
        /// </summary>
        /// <param name="articleTypeID"></param>
        /// <returns></returns>
        internal DataTable GetArticleSubType(String articleTypeID)
        {
            try
            {
                SqlConnection con = _connectData.GetConnect();
                string sql = "select  * from ArticleType where Superior = @articleTypeID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@articleTypeID", articleTypeID);

                return _connectData.GetDataTable(cmd);
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return null;
            }
            
        }


        /// <summary>
        /// get 1 top article type home : dành  bài viết trang chủ
        /// </summary>
        /// <param name="amount"></param>
        /// <param name="articleTypeID"></param>
        /// <returns>datatable</returns>
        public DataTable GetSomeTopArticleContentByType(String amount, String articleTypeID)
        {
            SqlConnection con = _connectData.GetConnect();

            try
            {
                string sql = "select top(@Amount) ProductID,Title,PostDate,Content from ArticleProductView where ProductStatusID=0 and ArticleTypeID=@ArticleTypeID ORDER BY ProductID DESC";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@Amount", Convert.ToInt16(amount));
                cmd.Parameters.AddWithValue("@ArticleTypeID", articleTypeID);
                DataTable dt = _connectData.GetDataTable(cmd);

                return dt;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return null;
            }
        }
    }
}